﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Runtime.CompilerServices;
using System.Collections.Specialized;
using System.Web.SessionState;
using System.Data;

public class SqlSiteMapProvider : StaticSiteMapProvider
{
    static readonly string _errmsg1 =
        "Missing connectionStringName attribute";
    static readonly string _errmsg2 = "Duplicate node ID";
    SiteMapNode _root = null;
    string _connect;

    /// <summary>
    /// BrowsingMode
    /// </summary>
    int _browsingMode;

    int _languageCode = 1033;

    enum BrowsingMode
    {
        IndexMode = 1,
        EntitiesMode = 2
    }

    public override void Initialize(string name,
        NameValueCollection attributes)
    {
        try
        { base.Initialize(name, attributes); }
        catch { }

        if (attributes == null)
            throw new ConfigurationException(_errmsg1);

        _connect = attributes["connectionStringName"];

        if (String.IsNullOrEmpty(_connect))
            throw new ConfigurationException(_errmsg1);


        if (attributes.AllKeys.Contains("LanguageCode"))
            Int32.TryParse(attributes["LanguageCode"], out _languageCode);

        if (attributes.AllKeys.Contains("BrowsingMode"))
            Int32.TryParse(attributes["BrowsingMode"], out _browsingMode);


    }

    /*
    [MethodImpl(MethodImplOptions.Synchronized)]
    public override SiteMapNode BuildSiteMap()
    {
        // Return immediately if this method has been called before
        if (_root != null) return _root;

        // Create a dictionary for temporary node storage and lookup
        Dictionary<int, SiteMapNode> nodes =
            new Dictionary<int, SiteMapNode>(16);

        // Query the database for site map nodes
        SqlConnection connection = new SqlConnection(
           ConfigurationManager.AppSettings[
               _connect].ToString());

        string sqlCommandText = "";

        try
        {
            if (_browsingMode == 1 || _browsingMode == 0)
                // In case that the View is by Index
                sqlCommandText = @"
                                    SELECT  SiteMapNodeID, 
                                            Name, 
                                            Url, 
                                            ParentSiteMapNodeID 
                                    FROM SiteMapNodes inner join itemdetails 
                                    ON TitleId = ItemDetails.ItemId where LanguageCode = " + _languageCode.ToString() + " Order By ParentSiteMapNodeId";
            else if (_browsingMode == 2)
                // In case that the View is by Entities.
                sqlCommandText = @"
                                    SELECT 0 AS SiteMapNodeId, 'Default' AS NAME, '_self' AS Url, NULL AS ParentSiteMapNodeId
                                    UNION
                                    SELECT SiteMapNodeID, Name, Url,0 AS ParentSiteMapNodeID 
                                    FROM SiteMapNodes inner join itemdetails 
                                    ON EntityId = ItemDetails.ItemId 
                                    --INNER JOIN dbo.ItemMaster ON dbo.SiteMapNodes.EntityId = dbo.ItemMaster.ItemId
                                    where LanguageCode = " + _languageCode.ToString() + @" --AND dbo.ItemMaster.CategoryId = 1
                                    --ORDER BY ItemMaster.ParentItemId
                                    ";
            connection.Open();
            SqlCommand command = new SqlCommand(sqlCommandText
                , connection);

            SqlDataReader reader = command.ExecuteReader();
            int id = reader.GetOrdinal("SiteMapNodeID");
            int url = reader.GetOrdinal("Url");
            int title = reader.GetOrdinal("Name");
            //int desc = reader.GetOrdinal("Description");
            //int roles = reader.GetOrdinal("Roles");
            int parent = reader.GetOrdinal("ParentSiteMapNodeID");

            if (reader.Read())
            {
                // Create the root SiteMapNode
                _root = new SiteMapNode(this,
                   reader.GetInt32(id).ToString(),
                   reader.IsDBNull(url) ? null : reader.GetString(url),
                   reader.GetString(title),
                   null);



                //  Add "*" to the roles list if no roles are specified
                if (_root.Roles == null) _root.Roles = new string[] { "*" };

                // Record the root node in the dictionary
                if (nodes.ContainsKey(reader.GetInt32(id)))
                    throw new ConfigurationException(_errmsg2);
                nodes.Add(reader.GetInt32(id), _root);

                // Add the node to the site map
                AddNode(_root, null);

                // Build a tree of SiteMapNodes underneath the root
                while (reader.Read())
                {
                    SiteMapNode node = new SiteMapNode(this,
                      reader.GetInt32(id).ToString(),
                      reader.IsDBNull(url) ? null : reader.GetString(url),
                      reader.GetString(title),
                          null);

                    //if (!reader.IsDBNull(roles))
                    //{
                    //    string rolenames =
                    //        reader.GetString(roles).Trim();
                    //    if (!String.IsNullOrEmpty(rolenames))
                    //    {
                    //        string[] rolelist = rolenames.Split
                    //            (new char[] { ',', ';' }, 512);
                    //        node.Roles = rolelist;
                    //    }
                    //}

                    // If the node lacks roles information,
                    // "inherit" that information from its parent

                    SiteMapNode parentnode =
                         nodes[reader.GetInt32(parent)];


                    // Record the node in the dictionary
                    if (nodes.ContainsKey(reader.GetInt32(id)))
                        throw new ConfigurationException(_errmsg2);
                    nodes.Add(reader.GetInt32(id), node);

                    // Add the node to the site map
                    AddNode(node, parentnode);
                }
            }
        }
        finally
        {
            connection.Close();
        }

        // Return the root SiteMapNode
        return _root;
    }
    */

    [MethodImpl(MethodImplOptions.Synchronized)]
    public override SiteMapNode BuildSiteMap()
    {
        // Return immediately if this method has been called before
        if (_root != null) return _root;

        // Create a dictionary for temporary node storage and lookup
        Dictionary<int, SiteMapNode> nodes =
            new Dictionary<int, SiteMapNode>(16);

        // Query the database for site map nodes
        SqlConnection connection = new SqlConnection(
           ConfigurationManager.AppSettings[
               _connect].ToString());

        string sqlCommandText = "";

        try
        {
            if (_browsingMode == 1 || _browsingMode == 0)
                // In case that the View is by Index
                sqlCommandText = @"
                                    SELECT  SiteMapNodeID, 
                                            Name, 
                                            Url, 
                                            ParentSiteMapNodeID 
                                    FROM SiteMapNodes inner join itemdetails 
                                    ON TitleId = ItemDetails.ItemId where LanguageCode = " + _languageCode.ToString() + " Order By ParentSiteMapNodeId, level";
            else if (_browsingMode == 2)
                // In case that the View is by Entities.
                /*sqlCommandText = @"
                                    SELECT 0 AS SiteMapNodeId, 'Default' AS NAME, '_self' AS Url, NULL AS ParentSiteMapNodeId
                                    UNION

                                    SELECT SiteMapNodeID, Name, Url,0 AS ParentSiteMapNodeID 
                                    FROM SiteMapNodes inner join itemdetails 
                                    ON EntityId = ItemDetails.ItemId 
                                    --INNER JOIN dbo.ItemMaster ON dbo.SiteMapNodes.EntityId = dbo.ItemMaster.ItemId
                                    where LanguageCode = " + _languageCode.ToString() + @" --AND dbo.ItemMaster.CategoryId = 1
                                    --ORDER BY ItemMaster.ParentItemId
                                    ";*/

                sqlCommandText = @"
SELECT 0 AS SiteMapNodeId, 'Default' AS NAME, '_self' AS Url, NULL AS ParentSiteMapNodeId
UNION
SELECT -1 AS SiteMapNodeID, 'Entities' AS NAME, '' AS Url, 0 AS ParentSiteMapNodeId
UNION
SELECT -2 AS SiteMapNodeID, 'Projects' AS NAME, '' AS Url, 0 AS ParentSiteMapNodeId
UNION
SELECT -3 AS SiteMapNodeID, 'General Data' AS NAME, '' AS Url, 0 AS ParentSiteMapNodeId
UNION
-- Contacts
SELECT SiteMapNodeID, Name, Url,-1 AS ParentSiteMapNodeID 
FROM SiteMapNodes inner join itemdetails 
ON EntityId = ItemDetails.ItemId
INNER JOIN ItemMaster 
ON ItemMaster.ItemId = ItemDetails.ItemId 
where LanguageCode = "+_languageCode.ToString()+@" AND ItemMaster.ParentItemId = 121
UNION
-- Projects
SELECT SiteMapNodeID, Name, Url,-2 AS ParentSiteMapNodeID 
FROM SiteMapNodes inner join itemdetails 
ON EntityId = ItemDetails.ItemId
INNER JOIN ItemMaster 
ON ItemMaster.ItemId = ItemDetails.ItemId 
where LanguageCode = " + _languageCode.ToString() + @" AND ItemMaster.ParentItemId = 122
UNION
-- General Data
SELECT SiteMapNodeID, Name, Url,-3 AS ParentSiteMapNodeID 
FROM SiteMapNodes inner join itemdetails 
ON EntityId = ItemDetails.ItemId
INNER JOIN ItemMaster 
ON ItemMaster.ItemId = ItemDetails.ItemId 
where LanguageCode = " + _languageCode.ToString() + @" AND ItemMaster.ParentItemId = 123
UNION
-- Other
SELECT SiteMapNodeID, Name, Url,0 AS ParentSiteMapNodeID 
FROM SiteMapNodes inner join itemdetails 
ON EntityId = ItemDetails.ItemId
INNER JOIN ItemMaster 
ON ItemMaster.ItemId = ItemDetails.ItemId 
where LanguageCode = " + _languageCode.ToString() + @" AND ItemMaster.ParentItemId = 124

";

            SqlCommand command = new SqlCommand(sqlCommandText
                , connection);

            SqlDataAdapter SiteMapAdapter = new SqlDataAdapter(command);

            DataTable dtResults = new DataTable();

            SiteMapAdapter.Fill(dtResults);

            // Loop to create all Nodes.
            

            try
            {

                DataRow drCurrentNode;

                // Loop to create all Nodes.
                for (int i = 0; i < dtResults.Rows.Count; i++)
                {
                    drCurrentNode = dtResults.Rows[i];

                    SiteMapNode node = new SiteMapNode(this, drCurrentNode["SiteMapNodeId"].ToString(), String.IsNullOrEmpty(drCurrentNode["Url"].ToString()) ? null : drCurrentNode["Url"].ToString(), String.IsNullOrEmpty(drCurrentNode["Name"].ToString()) ? "" : drCurrentNode["Name"].ToString(), null);

                    int NodeId = Convert.ToInt32(drCurrentNode["SiteMapNodeId"]);

                    if (!nodes.ContainsKey(NodeId))
                    {
                        nodes.Add(NodeId, node);
                    }
                }

                // Loop to Associate all nodes with their parent Nodes.
                for (int i = 0; i < dtResults.Rows.Count; i++)
                {
                    drCurrentNode = dtResults.Rows[i];

                    if (drCurrentNode["ParentSiteMapNodeID"] == DBNull.Value || drCurrentNode["ParentSiteMapNodeID"] == null || String.IsNullOrEmpty(drCurrentNode["ParentSiteMapNodeID"].ToString()))
                        continue;

                    SiteMapNode node = new SiteMapNode(this, drCurrentNode["SiteMapNodeId"].ToString(), String.IsNullOrEmpty(drCurrentNode["Url"].ToString()) ? null : drCurrentNode["Url"].ToString(), String.IsNullOrEmpty(drCurrentNode["Name"].ToString()) ? "" : drCurrentNode["Name"].ToString(), null);

                    int ParentSiteMapNodeID = Convert.ToInt32(drCurrentNode["ParentSiteMapNodeID"]);
                    try
                    {
                        AddNode(node, nodes[ParentSiteMapNodeID]);
                    }
                    catch { }
                }

                int RootSiteMapNodeID = Convert.ToInt32(dtResults.Select("ParentSiteMapNodeId IS NULL")[0]["SiteMapNodeId"]);

                _root = nodes[RootSiteMapNodeID];

                
            }
            catch (Exception exc)
            {

            }
        }
        catch (Exception Exc) { string message = Exc.Message; }

        return _root;
       
    }

    protected override SiteMapNode GetRootNodeCore()
    {
        BuildSiteMap();
        return _root;
    }
}
